SQL Search Script

In our data warehouse environment we have over 10 terabytes of data (staging and production reportable), comprised of over 5000 tables populated by over 500 SQL Agent jobs. So, when I get a question about a particular table I don't always know the answer from memory. I refer often to my SQL Search Script. This lets me know which tables, views, stored procedures and jobs relate to a particular search string. The only thing missing is SQL embedded in DTS.

DECLARE @SQL varchar (500)

DECLARE @Search varchar (200)

--Enter your search key-word or table name

SET @Search = 'SearchString'

DECLARE @DBName varchar (30)

DECLARE Databases CURSOR FAST_FORWARD FOR

IF OBJECT_ID('tempdb.dbo.#FindObjects') IS NOT NULL DROP TABLE #FindObjects

CREATE TABLE #FindObjects

(

Database_Name varchar(128) Null,

Obj_Name varchar(128) Null,

dbType char(1) Null

)

OPEN Databases

FETCH Databases INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'INSERT INTO #FindObjects '

SET @SQL = @SQL + 'SELECT DISTINCT ''' + @DBName + ''', [Name], a.Type '

SET @SQL = @SQL + 'FROM ' + @DBName + '.dbo.sysobjects a '

SET @SQL = @SQL + 'LEFT JOIN ' + @DBName + '.dbo.syscomments b ON a.ID = b.ID '

SET @SQL = @SQL + 'WHERE a.Type IN (''P'',''U'',''V'')'

SET @SQL = @SQL + 'AND (b.Text LIKE ' + '''' + '%' + @Search + '%' + ''''

SET @SQL = @SQL + 'OR a.Name LIKE ' + '''' + '%' + @Search + '%' + ''')'

EXEC(@SQL)

FETCH Databases INTO @DBName

END

CLOSE Databases

DEALLOCATE Databases

-- Objects Results

SELECT * FROM #FindObjects order by Database_Name, dbType, Obj_Name

-- Check Jobs

SELECT Job_Name = b.name, a.step_id, a.Step_Name, a.subSystem, a.command, a.database_name, a.output_file_name

FROM msdb.dbo.sysJobsteps a

Join msdb.dbo.sysjobs b on a.Job_id = b.Job_id

Where Step_name like '%' + @Search + '%'

or a.command like '%' + @Search + '%'

Dimensional Modeling

Why is it that the people you most want at a meeting are the ones who inevitably not show up? Such was the case when after 2 years of trying we finally received company financing to get the Kimball group to do on-site dimensional modeling training. The training was great, the benefits of adoption obvious and new energy was pumped into the staff to do things "the best way." The problem? Only one out of the five managers and directors who over-see the staff attended and never really catch the vision of adoption.

So, to keep the vision alive, I created the following power-point (Dimensional Modeling Powerpoint). I've used it a few times over the past year with BI staff and managers who missed the training and with new-hires. Maybe someone looking for a refresher course may find it useful as well.

Custom SSIS Slowly Changing Dimension Type 2

Unfortunately when you use the SCD wizard to create a type 2 slowly changing dimension you have to choose between using a current indicator or a valid date range technique. You can not have both, at least through the wizard. To add both you will have to edit the package after the wizard is complete.

Complete the Slowly Changing Dimension wizard. I prefer choosing the Single Column / Current Flag option for Historic Attribute Changes during the wizard.

Modify the components in the SDC update branch

1. Add a new derived column with an expression like (DT_DBTIMESTAMP) GETDATE()
2. Modify the OLEDB Command Update statement, i.e.:UPDATE [dbo].[tbl_Cost_Center_Dim] SET [Current_Flag] = ? ,Row_End_Date = ?WHERE [Cost_Center_Code] = ? AND [Current_Flag] = '1'
3. Modify the OLEDB Command, Column Mapping tab, to reflect the re-alignment the new parameter(s) added.

Modify the components in the SDC Insert path. These are the components after the Union All component.

1. Add new derived columns for begin and end effective dates. The end effective date should be a conformed standard future date such as 6/6/2079.
2. Add the new columns to the mapping of the OLEDB Insert Destination component.

In addition to using this process for Row Effective dates it is also useful for using Row Created and Row Updated dates.